﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class ForOverdue
    {
        public SqlDataAdapter SeeOverdue(string ReaderId="")
        {
            //查询所有逾期记录
            string sql = @"select Borrow.ReaderID,Reader.ReaderName,Book.BookName,Books.BookID,BorrowDate,GiveBackDate,ReturnDate 
                        from Borrow,Reader,Book,Books
                        where Borrow.BookID = Books.BookID 
                        and Borrow.ReaderID = Reader.ReaderID 
                        and Book.ID = Books.ID
                        and datediff(day,ReturnDate,GETDATE()) > 1";
            if(ReaderId != "")
            {
                sql = sql + string.Format(" and Borrow.ReaderID = '{0}'", ReaderId.ToString());
            }
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
        //按读者卡号查询借阅记录
        public SqlDataAdapter SearchBorrow(string ReaderId)
        {
            string sql = string.Format(@"select Borrow.ReaderID,Reader.ReaderName,Book.BookName,Books.BookID,BorrowDate,GiveBackDate,ReturnDate 
                        from Borrow,Reader,Book,Books 
                        where Borrow.BookID = Books.BookID 
                        and Borrow.ReaderID = Reader.ReaderID 
                        and Book.ID = Books.ID and Borrow.ReaderID like '%{0}%' 
                        and datediff(day,ReturnDate,GETDATE()) > 1 
                        and GiveBackDate is NULL
                        order by ReturnDate", ReaderId);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, DBHelper.Connection);
            return adapter;
        }
    }
}
